<?php
/**
 * Created by PhpStorm.
 * User: lixy
 * Date: 2015/12/29
 * Time: 17:27
 */

namespace app\models\weixin;

use Yii;
use app\lib\BaseModel;
class T_WXMALL_DORACARD extends BaseModel
{

//是否是销售人员for业务推广演示
    public static function get_User_Sale($openid){

        $cmd= static::query()->createCommand('select count(1) from T_WXMALL_USER_SALE t where openid=:openid');
        $cmd->bindValue(':openid', $openid);
        return $cmd->queryScalar();

    }
    //可购买卡片列表
    public static function get_available_Cards(){

        $cmd= static::query()->createCommand('select * from V_WXMALL_DORACARD t where ISTATUS=0 and DR=0 and ishow=\'Y\' order by price ');
        return $cmd->queryAll();

    }
    //可购买卡片列表
    public static function get_available_Cards_for_sale(){

        $cmd= static::query()->createCommand('select * from V_WXMALL_DORACARD t where ISTATUS=0 and DR=0  order by price ');
        return $cmd->queryAll();

    }
    //通过cardid查询单张卡片信息
    public static function get_cardByCardId($CARD_ID){

        $cmd=  static::query()->createCommand('select * from V_WXMALL_DORACARD t where ID=:CARD_ID');
        $cmd->bindValue(':CARD_ID', $CARD_ID);
        return $cmd->queryAll();
    }
    //通过cardid查询单张卡片单价
    public static function get_cardPriceByCardId($CARD_ID){

        $cmd=  static::query()->createCommand('select PRICE from T_WXMALL_DORACARD t where ID=:CARD_ID');
        $cmd->bindValue(':CARD_ID', $CARD_ID);
        return $cmd->queryScalar();
    }
    //通过卡片id查询可兑换产品列表
    public static function get_goodsByCardId($CARD_ID){

        $cmd=  static::query()->createCommand('select * from V_WXMALL_DORACARD_GOODS t where CARD_ID=:CARD_ID');
        $cmd->bindValue(':CARD_ID', $CARD_ID);
        return $cmd->queryAll();

    }
    //获取某张卡片某个产品详情
    public static function get_goodByCardIdAndGoodId($CARD_ID,$GOODCODE){

        //select * from V_WXMALL_DORACARD_GOODS where card_id='1001G210000000000ENT' and goods_code='DLHDQ0085'
        $cmd=  static::query()->createCommand('select * from V_WXMALL_DORACARD_GOODS where card_id=:CARD_ID and goods_code=:GOODCODE');

        $cmd->bindValue(':CARD_ID', $CARD_ID);
        $cmd->bindValue(':GOODCODE', $GOODCODE);
        return $cmd->queryAll();
    }
    //通过orderid判断此用户是否领取过此订单卡片  并返回cardcodeid
    public static function isReceive_card($ORDER_ID,$USER_ID){
        $cmd=  static::query()->createCommand('SELECT u.card_code_id   FROM t_wxmall_doracard_code h,t_wxmall_user_doracard_code u
        WHERE h.ID=u.CARD_CODE_ID AND h.order_id=:ORDER_ID AND u.user_id=:USER_ID');
        $cmd->bindValue(':ORDER_ID', $ORDER_ID);
        $cmd->bindValue(':USER_ID', $USER_ID);
        $re=$cmd->queryAll();
        if(count($re)==0){
            return false;  //没有领取
        }else{
            return $re[0]['CARD_CODE_ID'];
        }
    }
    //通过订单id查询卡片信息(注意：里面的userid是赠送人的)
    public static function get_cardByOrderId($ORDER_ID){

        $cmd=  static::query()->createCommand('select d.*,o.id as order_id,o.user_id,o.create_datetime as buytime  from V_WXMALL_DORACARD d,t_wxmall_order o where d.id=o.card_id and o.id=:ORDER_ID');
        $cmd->bindValue(':ORDER_ID', $ORDER_ID);
        return $cmd->queryAll();
    }
  //通过兑换码查询卡片信息(注意：里面的userid是赠送人的)
    public static function get_cardByCardCodeId($card_code_id){
        $cmd=  static::query()->createCommand('select d.*,o.id as order_id,o.user_id,o.blessing,o.create_datetime as buytime  from V_WXMALL_DORACARD d,t_wxmall_order o,t_wxmall_doracard_code c
      where d.id=o.card_id and o.id=c.order_id and c.id=:card_code_id');
        $cmd->bindValue(':card_code_id', $card_code_id);
        return $cmd->queryAll();
    }

    //通过orderid查询订单下的卡片数量
    public static function get_orderCardCountByOrderId($ORDER_ID){

        $cmd=  static::query()->createCommand('select count(1) from t_wxmall_doracard_code t where order_id=:ORDER_ID');
        $cmd->bindValue(':ORDER_ID', $ORDER_ID);
        return $cmd->queryScalar();
    }
    //通过orderid查询订单下的已领取卡片数量
    public static function get_orderGetCardCountByOrderId($ORDER_ID){

        $cmd=  static::query()->createCommand('select count(1) from t_wxmall_user_doracard_code
              where CARD_CODE_ID in (select id from t_wxmall_doracard_code t where order_id=:ORDER_ID) ');
        $cmd->bindValue(':ORDER_ID', $ORDER_ID);
        return $cmd->queryScalar();
    }
    //领取卡片列表
    public static function getReceiveCardListByOrderId($ORDER_ID){
        $cmd=  static::query()->createCommand('select u.header_url,u.nick_name,c.create_datetime from
             t_wxmall_doracard_code o,t_wxmall_user_doracard_code c,t_wxmall_user u
             where o.order_id=:ORDER_ID and o.id=c.card_code_id and c.user_id=u.id order by c.create_datetime desc');
        $cmd->bindValue(':ORDER_ID', $ORDER_ID);
        return $cmd->queryAll();
    }

    //送出的礼卡领取记录列表
    public static function getGetCardRecordListByOrderId($ORDER_ID){
        $cmd=  static::query()->createCommand('
            select t5.doracoin, t4.HEADER_URL, t4.NICK_NAME, t1.CREATE_DATETIME get_datetime, t2.CREATE_DATETIME exchange_datetime, t0.STATUS, t3.GOODS_NAME from T_WXMALL_DORACARD_CODE t0
            right join T_WXMALL_USER_DORACARD_CODE t1 on t0."ID"=t1.CARD_CODE_ID
            left join T_WXMALL_EXCHANGE_ORDER t2 on t2.CARD_CODE_ID=t0."ID"
            left join T_WXMALL_GOODS t3 on t3.GOODS_CODE=t2.GOODS_ID
            left join T_WXMALL_USER t4 on t4."ID"=t1.USER_ID
            left join T_WXMALL_DORACOIN_RECORD t5 on t5.order_code=t0.order_id
            where t0.ORDER_ID=:ORDER_ID');
        $cmd->bindValue(':ORDER_ID', $ORDER_ID);
        return $cmd->queryAll();
    }


    //获取cardcodeid 对应的状态
    public static function getGetStatusByCardCodeId($cardcodeid){
        $cmd=  static::query()->createCommand('select status from t_wxmall_doracard_code where id=:cardcodeid ');
        $cmd->bindValue(':cardcodeid', $cardcodeid);
        return $cmd->queryScalar();
    }





    //检查cardcodeid是否是当前用户的
    public static function verify_CardCodeIdAndUserID($CODEID,$USER_ID){
        $cmd=  static::query()->createCommand('SELECT count(*)   FROM t_wxmall_user_doracard_code u
        WHERE u.CARD_CODE_ID=:CODEID AND u.user_id=:USER_ID');
        $cmd->bindValue(':CODEID', $CODEID);
        $cmd->bindValue(':USER_ID', $USER_ID);
        return $cmd->queryScalar() >0 ;
    }
    //通过card_code_id查询订单id
    public static function get_OrderidByCardCodeId($CODEID){

        $cmd=  static::query()->createCommand('select order_id from t_wxmall_doracard_code where id=:CODEID');
        $cmd->bindValue(':CODEID', $CODEID);
        return $cmd->queryScalar();
    }
    //oracle存储过程    2016-1-25增加：订单总价
    //生成卡片订单 返回订单编号 失败返回0
    public static function create_order($cardid,$count,$totalprice,$userid){

        $cmd=  static::query()->createCommand('call p_wxmall_create_order( :cardid,:count,:userid,:totalprice,:orderid)');
        $cmd->bindValue(':cardid',$cardid);
        $cmd->bindValue(':count',$count);
        $cmd->bindValue(':userid',$userid);
        $cmd->bindValue(':totalprice',$totalprice);
        $cmd->bindParam(':orderid', $orderid,\PDO::PARAM_STR,50);
        $cmd->execute();
//        Yii::trace($orderid);
        return $orderid;
    }
    //oracle存储过程
    //接收卡片   成功返回1  失败返回 汉字
    public static function create_RECEIVE_CARD($orderid,$userid){

        $cmd=  static::query()->createCommand('call P_RECEIVE_CARD( :ORDERID,:USERID,:re)');
        $cmd->bindValue(':ORDERID',$orderid);

        $cmd->bindValue(':USERID',$userid);
        $cmd->bindParam(':re', $re,\PDO::PARAM_STR,50);
        $cmd->execute();

        return $re;
    }

/*P_CARD_EXCHANGE
(
  USERID IN VARCHAR2              --用户id
, CARDCODE IN VARCHAR2            --凭证编号
, GOODID IN VARCHAR2              --产品编码
, RECEIVERNAME IN VARCHAR2       --收货人
, RECEIVERPHONE IN VARCHAR2      --收货人电话
, RECEIVERADDRESS IN VARCHAR2    --收货地址
, CREMARK IN VARCHAR2            --备注
, RESULTS OUT VARCHAR2           --返回值
, PROVINCE IN VARCHAR2           --收货省
, CITY IN VARCHAR2               --收货市
, REGION IN VARCHAR2             --收货区
)
*/
//兑换产品  1成功 0失败
    public static function P_CARD_EXCHANGE($userid,$codeid,$goodid,$RECEIVERNAME,$RECEIVERPHONE,$RECEIVERADDRESS,$CREMARK,$sheng,$shi,$qu){
        $cmd=  static::query()->createCommand('call P_CARD_EXCHANGE(:userid,:codeid,:goodid,:RECEIVERNAME,:RECEIVERPHONE,:RECEIVERADDRESS,:CREMARK,:re,:sheng,:shi,:qu)');
        $cmd->bindValue(':userid',$userid);
        $cmd->bindValue(':codeid',$codeid);
        $cmd->bindValue(':goodid',$goodid);
        $cmd->bindValue(':RECEIVERNAME',$RECEIVERNAME);
        $cmd->bindValue(':RECEIVERPHONE',$RECEIVERPHONE);
        $cmd->bindValue(':RECEIVERADDRESS',$RECEIVERADDRESS);
        $cmd->bindValue(':CREMARK',$CREMARK);
        $cmd->bindValue(':sheng',$sheng);
        $cmd->bindValue(':shi',$shi);
        $cmd->bindValue(':qu',$qu);

        $cmd->bindParam(':re', $re,\PDO::PARAM_STR,50);
        $cmd->execute();

        return $re;
    }
    //设置订单nc同步状态
    public static function setNC_SYNSTATUS($card_code_id,$SYNSTATUS){
        $cmd=  static::query()->createCommand('update t_wxmall_exchange_order set SYNSTATUS=:SYNSTATUS where card_code_id=:card_code_id');
        $cmd->bindValue(':SYNSTATUS', $SYNSTATUS);
        $cmd->bindValue(':card_code_id', $card_code_id);
        return $cmd->execute();
    }
    //获取卡片信息  通过cardcodeid
    public static function getPriceAndCardIdByCardcodeid($cardcodeid){
//        select c.price,c.id from t_wxmall_doracard c,t_wxmall_doracard_code d where c.id=d.card_id and d.id='1001G210000000000ENZ1050'
        $cmd=  static::query()->createCommand('select c.price,c.id from t_wxmall_doracard c,t_wxmall_doracard_code d
          where c.id=d.card_id and d.id=:cardcodeid');
        $cmd->bindValue(':cardcodeid', $cardcodeid);
        return $cmd->queryAll();
    }
    //区域代码获取
    public static function get_AreaList_sheng(){
        //SELECT d.doccode 编号,d.docname 名称,d.pk_defdoc 档案PK FROM bd_defdoc d
//        WHERE d.pk_defdoclist = '0001NC1000000001QXG6' and d.pk_defdoc1 is null
        $cmd=  static::query()->createCommand('SELECT d.doccode ,d.docname ,d.pk_defdoc  FROM bd_defdoc d WHERE d.pk_defdoclist = \'0001NC1000000001QXG6\' and d.pk_defdoc1 is null');
//        $cmd->bindValue(':userid', $userid);
        return $cmd->queryAll();
    }
    public static function get_AreaList_other($pk_defdoc){
//        SELECT d.doccode 编号,d.docname 名称,d.pk_defdoc 档案PK
//FROM bd_defdoc d WHERE d.pk_defdoc1 = '0001NCX1000001X02684'
        $cmd=  static::query()->createCommand('SELECT d.doccode ,d.docname ,d.pk_defdoc FROM bd_defdoc d WHERE d.pk_defdoc1 = :pk_defdoc');
        $cmd->bindValue(':pk_defdoc', $pk_defdoc);
        return $cmd->queryAll();
    }



    //通过userid查询订单列表   我的购买列表
    public static function get_OrderByUserid($userid){

        $cmd=  static::query()->createCommand('select * from t_wxmall_order  where user_id=:userid and STATUS=\'2\' order by CREATE_DATETIME desc');
        $cmd->bindValue(':userid', $userid);
        return $cmd->queryAll();
    }
    //通过订单编号查询购买数量
    public static function get_CodeCountByOrderId($orderid){
        $cmd=  static::query()->createCommand('select count(1) from t_wxmall_doracard_code where order_id=:orderid');
        $cmd->bindValue(':orderid', $orderid);
        return $cmd->queryScalar();
    }
    //通过订单编号查询有效天数
    public static function get_VALIDDaysByOrderId($orderid){
        $cmd=  static::query()->createCommand('select (TO_DATE(end_datetime,\'yyyy-mm-dd\')-trunc(sysdate)) VALID_days from t_wxmall_doracard_code where order_id=:orderid');
        $cmd->bindValue(':orderid', $orderid);
        $re=$cmd->queryScalar();
        if($re==null || $re==false){
            return 0;
        }else{
            return $re;
        }
    }


    //通过userid查询领取卡片列表   我的领取列表
    public static function get_BagCardsByUserid($userid,$status){

            $statussql="c.status in ($status)";



        $cmd=  static::query()->createCommand("select u.*,c.card_id,c.order_id,c.status from t_wxmall_user_doracard_code u,t_wxmall_doracard_code  c where u.card_code_id=c.id
                and u.user_id=:userid and $statussql order by u.CREATE_DATETIME desc");
        $cmd->bindValue(':userid', $userid);

        return $cmd->queryAll();
    }

    //通过card_code_id查询兑换的产品信息
    public static function get_GoodByCardCodeId($cardcodeid){
        $cmd=  static::query()->createCommand('select g.goods_code,
         d.name,d.valid_date_message,d.price,g.goods_name,g.goods_img_url,o.RECEIVER_PROVINCE sheng,o.RECEIVER_CITY shi,o.RECEIVER_REGION qu
         ,o.RECEIVER_NAME,o.RECEIVER_PHONE,o.RECEIVER_ADDRESS,o.CREATE_DATETIME,bo.create_datetime bocreate_datetime
         ,u.nick_name,u.header_url
        from t_wxmall_exchange_order o,t_wxmall_doracard_code c,v_wxmall_doracard_goods g,t_wxmall_doracard d,t_wxmall_order bo,t_wxmall_user u
         where o.card_code_id=c.id and g.card_id=c.card_id and g.goods_code=o.goods_id and d.id=c.card_id and bo.id=c.order_id and bo.user_id=u.id(+)
         and o.card_code_id=:card_code_id');
        $cmd->bindValue(':card_code_id', $cardcodeid);
        return $cmd->queryAll();
    }

    public static function get_DoracoinInfo($card_code_id){
        $cmd=  static::query()->createCommand('
            SELECT t2.PRICE, t0.ORDER_ID, t0."ID" FROM T_WXMALL_DORACARD_CODE t0
            left join T_WXMALL_DORACARD t2 on t2."ID"=t0.CARD_ID
            WHERE t0."ID"=:id');
        $cmd->bindValue(':id', $card_code_id);
        return $cmd->queryAll();
    }
    //根据订单编号查询订单
    public static function getOrderInfoByOrderCode($ORDERCODE,$user_id){
        $cmd= static::query()->createCommand('select * from T_WXMALL_ORDER where user_id=:user_id and id=:code');
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':code',$ORDERCODE);
        return $cmd->queryAll();
    }
    //根据订单编号查询订单
    public static function getOrderInfoByOrderCode_base($ORDERCODE){
        $cmd= static::query()->createCommand('select * from T_WXMALL_ORDER where  id=:code');
        $cmd->bindValue(':code',$ORDERCODE);
        return $cmd->queryAll();
    }
    //添加订单的祝福语
    public static function add_order_BLESSING($ORDERCODE,$user_id,$BLESSING){
        $cmd= static::query()->createCommand('update  t_wxmall_order set BLESSING=:BLESSING where id=:code and user_id=:user_id ');
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':code',$ORDERCODE);
        $cmd->bindValue(':BLESSING',$BLESSING);
        return $cmd->execute();
    }

    //oracle存储过程   线下卡片
    //接收卡片   成功返回1  失败返回 汉字
    public static function P_RECEIVE_CARD_OFFLINE($card_code_id,$userid){

        $cmd=  static::query()->createCommand('call P_RECEIVE_CARD_OFFLINE( :doraCardCode,:USERID,:re)');
        $cmd->bindValue(':doraCardCode',$card_code_id);

        $cmd->bindValue(':USERID',$userid);
        $cmd->bindParam(':re', $re,\PDO::PARAM_STR,50);
        $cmd->execute();

        return $re;
    }

    //查询t_wxmall_doracard_voucher
//private String voucher_id;
//private String order_id;     //--绑定的订单表
//private String order_code_id;//--绑定的订单凭证表ID
//private String voucher;      //单证号
//private String token;        //密码
//private Integer vstatus;     //--单证状态 0：未绑定未激活 1：已绑定 2：已激活 3：已过期 4：已兑换
    public static function get_t_wxmall_doracard_voucher($card_voucher,$card_token){
        $cmd=  static::query()->createCommand(
            'select * from t_wxmall_doracard_voucher  where voucher=:voucher and token=:token and vstatus=2'
        );
        $cmd->bindValue(':voucher',$card_voucher);
        $cmd->bindValue(':token',$card_token);
        return $cmd->queryAll();
    }
    //是否是线下卡片
    public static function isOfflineCard($card_code_id){
        $cmd=  static::query()->createCommand(
            'select count(1) from t_wxmall_doracard_voucher  where order_code_id=:order_code_id and vstatus=2'
        );
        $cmd->bindValue(':order_code_id',$card_code_id);

        return $cmd->queryScalar() > 0;
    }
//是否线下卡片已被领取
    public static function isReceive_card_offline($card_code_id){
        $cmd=  static::query()->createCommand(
            'select count(1) from t_wxmall_user_doracard_code  where CARD_CODE_ID=:card_code_id'
        );
        $cmd->bindValue(':card_code_id',$card_code_id);

        return $cmd->queryScalar() > 0;
    }

    /**
     * 获取所有端午礼卡
     * @return array
     */
    public static function getDragonCardList(){
        $cmd=  static::query()->createCommand('
            SELECT t1.* FROM T_WX_EXTEND_CATEGORY_RELA t0
            LEFT JOIN V_WXMALL_DORACARD t1 ON t1."ID"=t0.RELATION_ID
            WHERE t0.CATEGORY_ID=\'duanwu\' AND t0.RELATION_TYPE=1');
        $result = $cmd->queryAll();

        return $result;
    }
}